﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Threading;
using System.Windows.Forms;

namespace DbView
{
	/*
	TODO:
		3. Computed columns
		4. Non-null columns - have to get the defaults and add them manually. Doesn't work for eg newid().
			- need an experimental bulk-copy project.
	*/
	public partial class ImportCsvForm : Form
	{
		delegate bool ProgDelegate(String operation, int step, int steps, int state);
		ProgDelegate d;
		bool userCancel = false;
		Thread importWorkerThread;
		// for testing set this at each value. The OI should set it
		DbViewDriver.CsvImportMode m_ImportMode = DbViewDriver.CsvImportMode.Replace;

		public ImportCsvForm(DbViewDriver.IDatabaseExportSource database, string table, string file)
		{
			this.InitializeComponent();
			this.Database = database;
			this.TableName = table;
			this.InputCsvFile = file;
			this.Mappings = new List<int>();
			this.lblInFile.Text = file;
			this.lblTable.Text = table;
			this.lblHelp.Text = TITLE;
		}

		// import mode: overwrite clears old data, append just adds new records, update adds and updates
		// update requires a pkey. append is dodgy without one.
		// public enum mode {overwrite, append, update };
		DbViewDriver.IDatabaseExportSource Database { get; set; }
		private string Connection { get; set; }
		private string TableName { get; set; }
		private string InputCsvFile { get; set; }
		private List<int> Mappings { get; set; }  // input column m -> Table Field
		private String[] HeaderCells { get; set; }
		private bool HasHeader { get { return chkHeader.Checked; } }

		private void ImportCsvForm_Load(object sender, EventArgs e) { SetupControls(); }
		private void button1_Click(object sender, EventArgs e) { this.Close(); }
		private void button2_Click(object sender, EventArgs e)
		{
			if (IsWorking())
				DoCancel();
			else
				DoImport();
		}


		private void btnBrowse_Click(object sender, EventArgs e) { SelectInputFile(); }

		private void chkHeader_CheckedChanged(object sender, EventArgs e) { DisplayImportData(chkHeader.Checked); }
		private void chkHide_CheckedChanged(object sender, EventArgs e) { HideUnhideMapped(sender as CheckBox); }

		private void SetupControls()
		{
			// Test connection, table name and rowCount
			string message = "";
			if (!ConnectionAndRowCountCheckOk(TableName, out message))
			{
				MessageBox.Show(this, message, "Cannot proceed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				this.Close();
				return;
			}

			bool isFileSelected = (InputCsvFile.Length > 0);
			SetInputFileControls(isFileSelected);
			// If no input file entered then disable dialog and prompt user to select a file.
			if (!isFileSelected)
			{
				return;
			}

			// If bad then abort this method and wait for a proper one to be selected
			if (!File.Exists(InputCsvFile))
			{
				MessageBox.Show(this, "Please select a valid CSV file", "Input file not found", MessageBoxButtons.OK, MessageBoxIcon.Information);
				return;
			}


			// Set whatever control contains the import mode choice 
			comboMode.SelectedIndex = MapModeToIndex(this.m_ImportMode);

			try
			{
				// populate the input and preview tables.
				int sampleLineCount = 10; // Show this number of lines from the import file
				List<String> fields = GetFieldNames(TableName);
				DataTable dt = null;

				System.Text.Encoding fileEnc = GetFileEncoding(InputCsvFile);
				StreamReader sr = new StreamReader(InputCsvFile, fileEnc);
				string input;
				// parse input
				Csv.CsvReader reader = new Csv.CsvReader();
				reader.FieldPadding = false;
				bool done = true;
				int lineCount = 0;
				while ((input = sr.ReadLine()) != null && lineCount++ < sampleLineCount)
				{
					done = reader.ParseCsvLine(input);
					if (done)
					{
						String[] cells = reader.Data;
						if (lineCount <= 1)
						{
							HeaderCells = cells;
							dt = MakeImportTable(cells.GetLength(0));
						}
						dt.Rows.Add(cells);
					}
				}
				sr.Close();
				this.dgvInput.DataSource = dt;

				// Mappings from import file columns to table columns
				ClearMappings(); // initialise mappings to null

				// Get the schema for the target table
				DataTable dt2 = this.MakePreviewTable(lineCount);
				this.dgvPreview.DataSource = dt2;

				// disable sorting
				DisableSorting(dgvInput);
				DisableSorting(dgvPreview);

				if (!IsHeaderPossible(dt))
				{
					this.chkHeader.Checked = false;
					this.chkHeader.Enabled = false;
				}
				else
				{
					// make an intelligent guess about the header row.
					this.chkHeader.Enabled = true;
					this.chkHeader.Checked = GuessHeaderState(dt, dt2);
					DisplayImportData(this.chkHeader.Checked);
					// if we think there is a header row do the automatch
					if (this.chkHeader.Checked)
						DoAutoMatch(true);
				}
			}
			catch (Exception e)
			{
				string msg = @"Error preparing form for import configuration.
There was a problem accessing either the file 
or the table. Please check the message that follows
and make the appropriate adjustments
{0}";
				MessageBox.Show(String.Format(msg, e.Message), "Error loading data", MessageBoxButtons.OK, MessageBoxIcon.Error);
				this.Close();
			}
		}

		private void SetInputFileControls(bool isFileSelected)
		{
			lblInFile.ForeColor = (!isFileSelected) ? System.Drawing.Color.Red : lblTable.ForeColor;
			lblInFile.TextAlign = (!isFileSelected) ? ContentAlignment.TopRight : lblTable.TextAlign;
			if (!isFileSelected)
				lblInFile.Text = "Select the CSV file to import ->";
			comboMode.Enabled = isFileSelected;
			btnImport.Enabled = isFileSelected;
			btnMatch.Enabled = isFileSelected;
			chkHeader.Enabled = isFileSelected;
		}

		private int MapModeToIndex(DbViewDriver.CsvImportMode m_ImportMode)
		{
			return (this.m_ImportMode == DbViewDriver.CsvImportMode.Replace) ? 0 :
					(this.m_ImportMode == DbViewDriver.CsvImportMode.Merge) ? 1 :
					(this.m_ImportMode == DbViewDriver.CsvImportMode.AppendOnly) ? 2 :
					(this.m_ImportMode == DbViewDriver.CsvImportMode.UpdateOnly) ? 3 : -1;

		}
		private DbViewDriver.CsvImportMode MapModeToIndex(int index)
		{
			return (index == 0) ? DbViewDriver.CsvImportMode.Replace :
					(index == 1) ? DbViewDriver.CsvImportMode.Merge :
					(index == 2) ? DbViewDriver.CsvImportMode.AppendOnly :
					(index == 3) ? DbViewDriver.CsvImportMode.UpdateOnly : DbViewDriver.CsvImportMode.AppendOnly;
		}

		private void DisableSorting(DataGridView dgv)
		{
			foreach (DataGridViewColumn col in dgv.Columns)
				col.SortMode = DataGridViewColumnSortMode.NotSortable;
			return;
		}

		// Thanks to Rick Strahl. Blog post http://www.west-wind.com/weblog/posts/2007/Nov/28/Detecting-Text-Encoding-for-StreamReader
		// or search:  strahl encoding streamreader
		/// <summary>
		/// Detects the byte order mark of a file and returns
		/// an appropriate encoding for the file.
		/// </summary>
		/// <param name="srcFile"></param>
		/// <returns></returns>
		public static System.Text.Encoding GetFileEncoding(string srcFile)
		{
			// *** Use Default of Encoding.Default (Ansi CodePage)
			System.Text.Encoding enc = System.Text.Encoding.Default;
			// *** Detect byte order mark if any - otherwise assume default
			byte[] buffer = new byte[5];
			FileStream file = new FileStream(srcFile, FileMode.Open);
			file.Read(buffer, 0, 5);
			file.Close();
			if (buffer[0] == 0xef && buffer[1] == 0xbb && buffer[2] == 0xbf)
				enc = System.Text.Encoding.UTF8;
			else if (buffer[0] == 0xfe && buffer[1] == 0xff)
				enc = System.Text.Encoding.Unicode;
			else if (buffer[0] == 0 && buffer[1] == 0 && buffer[2] == 0xfe && buffer[3] == 0xff)
				enc = System.Text.Encoding.UTF32;
			else if (buffer[0] == 0x2b && buffer[1] == 0x2f && buffer[2] == 0x76)
				enc = System.Text.Encoding.UTF7;
			return enc;
		}

		void UpdateImportMode()
		{
			this.m_ImportMode = MapModeToIndex(comboMode.SelectedIndex);
		}

		// To prevent an error when clicking has header and the 1st data line is missing
		// data in some cells make this check first and disable the check box
		private bool IsHeaderPossible(DataTable importDataTable)
		{
			// Header is NOT possible if there are no rows of data.
			if (importDataTable.Rows.Count == 0)
				return false;
			// assume at least 1 column and at least one row
			DataRow dr = importDataTable.Rows[0];
			foreach (object o in dr.ItemArray)
			{
				string name = o.ToString().ToUpper();
				if (name.Length == 0) return false;
			}
			return true;
		}

		private bool GuessHeaderState(DataTable importDataTable, DataTable previewDataTable)
		{
			// Detection of Header
			// a. all header columns contain A-Z (use regex)
			// b. at least one column is all numeric.
			// assume at least 1 column and at least one row
			DataRow dr = importDataTable.Rows[0];
			double testNumber;
			DateTime testDate;
			int columnMatchCount = 0;
			foreach (object o in dr.ItemArray)
			{
				string name = o.ToString().ToUpper();
				// a column name cannot be empty, but data may well be empty
				// this is enough to make the guess that the row is data not a header.
				if (name.Length == 0) return false;
				// if the data parses to a number or date then its most likely data.
				if (double.TryParse(name, out testNumber)) return true;
				if (DateTime.TryParse(name, out testDate)) return true;
				// match row 1 data to target field list in preview table
				if (previewDataTable.Columns.Contains(o.ToString()))
					++columnMatchCount;
			}
			// Column match count. Input may have more columns. table may have more columns.
			// if as many columns match as can then this is good enough
			// It covers export from DbViewSharp and import into. So its seemless working 
			// within the context of the app. 
			if (columnMatchCount == previewDataTable.Columns.Count ||
				columnMatchCount == importDataTable.Columns.Count)
				return true;

			// Here with non-numeric or date data in the first row.
			// if all of the other rows contains only text then for safety we'll assume row 1 is data
			foreach (DataColumn dc in importDataTable.Columns)
			{
				bool firstRow = true;
				bool oneParse = false; // at least one column parses to a date or number
				bool allParse = true;  // all non-empty items tested parse to a number or date
				foreach (DataRow dr2 in importDataTable.Rows)
				{
					if (firstRow)  // don't parse the first row. it's our suspected header!
						firstRow = false;
					else
					{
						string data = dr2[dc].ToString();
						if (data.Length > 0) // cannot parse empty
						{
							// its a number or date
							if (double.TryParse(data, out testNumber) || DateTime.TryParse(data, out testDate))
								oneParse = true;
							else
								// it isn't a number or date.
								allParse = false;
						}
					}
				}
				// if one column has all number/date info and/or emty then this is enough to confirn that
				// there is a header row
				if (oneParse && allParse) return true;
			}

			return false;  // give up. let the user decide.
		}



		// build a string only table for the given number of columns
		// The generic column titles will be overridden if the import file contains a header row.
		DataTable MakeImportTable(int cellCount)
		{
			string columns = "", columnTypes = "";
			int column = 0;
			for (int idx = 0; idx < cellCount; ++idx)
			{
				if (columns.Length > 0) columns += "|";
				columns += string.Format("Column {0}", column + 1);
				columnTypes += "S";
				++column;
			}
			DataTable dt = DataTableHelpers.MakeDataTable(TableName, columns, columnTypes);
			return dt;
		}

		private void DisplayImportData(bool hasHeader)
		{
			DataTable dt = dgvInput.DataSource as DataTable;
			if (dt == null) return; // file not selected
			int column = 0;
			foreach (DataColumn dc in dt.Columns)
			{
				// assume generic column name
				string columnName = string.Format("Column {0}", column + 1);
				// but override if the import file has a header row
				if (hasHeader)
					columnName = dt.Rows[0][column].ToString();
				dc.ColumnName = columnName;
				++column;
			}

			// hide the first row in the table if it is the header row
			// as the information is now in the column titles.
			if (hasHeader)
			{
				dgvInput.CurrentCell = null;
				dgvInput.Rows[0].Visible = false;
			}
			// despite disabling the grid before something in here resets sorting on
			// Its probably the column name change. So unset again.
			DisableSorting(dgvInput);
		}

		// Build a table to show the results of the mappings.
		private DataTable MakePreviewTable(int previewRowCount)
		{

			String[] fields = GetFieldNames(TableName).ToArray();
			// prepeare the column name parameter for the table creater Col1|Col2..|Col9
			string fieldList = String.Join("|", fields);
			// make all the columns strings.	
			string defs = new String('S', fields.GetLength(0));

			DataTable dt = DataTableHelpers.MakeDataTable("Preview", fieldList, defs);
			// naughty reuse of fields array. Clear them to make empty rows
			// as the user maps columns these will be populated.
			for (int idx = 0; idx < fields.GetLength(0); ++idx)
				fields[idx] = "";

			// add the blank preview riws.
			for (int idx = 0; idx < previewRowCount; ++idx)
				dt.Rows.Add(fields);
			return dt;
		}

		// fetch fields from the database source
		private List<string> GetFieldNames(string tableName)
		{
			DbView.FieldInfo[] fields = Database.GetSchema(TableName);
			List<String> l = new List<String>();
			foreach (DbView.FieldInfo field in fields)
			{
				l.Add(field.Name);
			}
			return l;
		}

		private void SelectInputFile()
		{
			OpenFileDialog openFileDialog = new OpenFileDialog();

			openFileDialog.FileName = InputCsvFile;
			openFileDialog.Filter = "CSV Files (*.csv)|*.csv";
			openFileDialog.RestoreDirectory = true;

			if (openFileDialog.ShowDialog() == DialogResult.OK)
			{
				if (new FileInfo(openFileDialog.FileName).Length == 0)
					MessageBox.Show(this, "There is no data in " + openFileDialog.FileName + " to import", "Input file is empty", MessageBoxButtons.OK, MessageBoxIcon.Information);
				else
				{
					InputCsvFile = openFileDialog.FileName;
					lblInFile.Text = InputCsvFile;
					SetupControls();
				}
			}
		}


		//////////////////////////////////////////////////////////////////
		// Button clicks

		// AutoMatch
		private void btnMatch_Click(object sender, EventArgs e)
		{
			DoAutoMatch(chkHeader.Checked);
		}

		private void DoAutoMatch(bool hasHeader)
		{
			// start again.
			ClearMappings();

			DataTable src = dgvInput.DataSource as DataTable;
			DataTable tgt = dgvPreview.DataSource as DataTable;
			if (src == null) return; // file not selected

			// Scan the import table columns
			for (int idxs = 0; idxs < src.Columns.Count; ++idxs)
			{
				// If there is a header search the database table columns for an exact match
				if (hasHeader)
				{
					string srcName = src.Columns[idxs].ColumnName;
					bool found = false;
					for (int idxt = 0; idxt < tgt.Columns.Count && !found; ++idxt)
					{
						// alwayts case insensitive matching.
						if (tgt.Columns[idxt].ColumnName.ToUpper() == srcName.ToUpper())
						{
							// connect
							Mappings[idxs] = idxt;
							found = true;
						}
					}
				}
				// Generic columns. Just map in sequence and let the user adjust as necessary
				else
				{
					if (idxs < tgt.Columns.Count) Mappings[idxs] = idxs;
				}
				// update the preview table column mapped to by the current source table column
				UpdateMapDisplay(idxs);
			}
			// clear from the preview table any previously populated, but non-automatched colums
			// probaby a rare event, but tidier to address
			for (int idxt = 0; idxt < tgt.Columns.Count; ++idxt)
				if (!Mappings.Contains(idxt))
					ClearDisplay(idxt);
		}

		private void ClearMappings()
		{
			for (int idx = 0; idx < Mappings.Count; ++idx)
				Mappings[idx] = -1;
			while (Mappings.Count < dgvInput.Columns.Count)
				Mappings.Add(-1);
		}

		private void HideUnhideMapped(CheckBox checkBox)
		{
			bool visible = !checkBox.Checked;
			for (int idx = 0; idx < Mappings.Count; ++idx)
			{
				if (Mappings[idx] != -1)
				{
					dgvInput.Columns[idx].Visible = visible;
					dgvPreview.Columns[Mappings[idx]].Visible = visible;

				}
			}
		}

		void UpdateMapDisplay(int sourceColumn)
		{
			if (sourceColumn == -1) return;
			int previewColumn = Mappings[sourceColumn];

			DataTable inp = dgvInput.DataSource as DataTable;
			DataTable dt = dgvPreview.DataSource as DataTable;

			int row = (HasHeader) ? 1 : 0;
			foreach (DataRow dr in dt.Rows)
			{
				if (row < inp.Rows.Count)
				{
					if (previewColumn != -1)
						dr[previewColumn] = inp.Rows[row++][sourceColumn].ToString();
				}
			}
			HideUnhideMapped(chkHide);
		}

		void ClearDisplay(int previewColumn)
		{
			if (previewColumn == -1) return;
			DataTable dt = dgvPreview.DataSource as DataTable;
			foreach (DataRow dr in dt.Rows)
			{
				dr[previewColumn] = "";
			}
		}


		private void dgvInput_MouseClick(object sender, MouseEventArgs e)
		{
			DataGridView dgv = (DataGridView)sender;
			System.Windows.Forms.DataGridView.HitTestInfo hti;
			hti = dgv.HitTest(e.X, e.Y);

			if (e.Button == System.Windows.Forms.MouseButtons.Right)
			{
			}
			if (e.Button == System.Windows.Forms.MouseButtons.Right)
				if (hti.Type == DataGridViewHitTestType.ColumnHeader)
				{
					MakeInputColumnPopup(hti.ColumnIndex).Show(dgv, new Point(e.X, e.Y));
				}
		}

		public System.Windows.Forms.ContextMenu MakeInputColumnPopup(int hitColumnIndex)
		{
			ContextMenu cm = new ContextMenu();
			// find all unmapped columns in database
			if (Mappings[hitColumnIndex] == -1)
			{
				foreach (DataGridViewColumn dgvc in dgvPreview.Columns)
				{
					if (!Mappings.Contains(dgvc.Index))
					{
						MenuItem mi = new MenuItem("Map to " + dgvc.Name);
						mi.Tag = string.Format("{0},{1}", hitColumnIndex, dgvc.Index);
						mi.Click += MapColumn_Click;
						cm.MenuItems.Add(mi);
					}
				}
			}
			else
			{
				MenuItem mi = new MenuItem("Unmap");
				mi.Tag = string.Format("{0}", hitColumnIndex);
				mi.Click += UnmapInputColumn_Click;
				cm.MenuItems.Add(mi);
			}


			return cm;
		}

		private void dgvPreview_MouseClick(object sender, MouseEventArgs e)
		{
			DataGridView dgv = (DataGridView)sender;
			System.Windows.Forms.DataGridView.HitTestInfo hti;
			hti = dgv.HitTest(e.X, e.Y);

			if (e.Button == System.Windows.Forms.MouseButtons.Right)
			{
			}
			if (e.Button == System.Windows.Forms.MouseButtons.Right)
				if (hti.Type == DataGridViewHitTestType.ColumnHeader)
				{
					MakePreviewColumnPopup(hti.ColumnIndex).Show(dgv, new Point(e.X, e.Y));
				}
		}

		public System.Windows.Forms.ContextMenu MakePreviewColumnPopup(int hitColumnIndex)
		{
			ContextMenu cm = new ContextMenu();
			// find all unmapped columns in database
			if (!Mappings.Contains(hitColumnIndex))
			{
				foreach (DataGridViewColumn dgvc in dgvInput.Columns)
				{
					if (Mappings[dgvc.Index] == -1)
					{
						MenuItem mi = new MenuItem("Map to " + dgvc.Name);
						mi.Tag = string.Format("{0},{1}", hitColumnIndex, dgvc.Index);
						mi.Click += MapColumnToImport_Click;
						cm.MenuItems.Add(mi);
					}
				}
			}
			else
			{
				MenuItem mi = new MenuItem("Unmap");
				mi.Tag = string.Format("{0}", hitColumnIndex);
				mi.Click += UnmapColumn_Click;
				cm.MenuItems.Add(mi);
			}

			return cm;
		}

		private void MapColumn_Click(object sender, System.EventArgs e)
		{
			// only for input grid
			// only for preview grid
			MenuItem mi = sender as MenuItem;
			// get column code
			String[] data = mi.Tag.ToString().Split(',');

			int inpIndex = System.Convert.ToInt32(data[0]);
			int previewIndex = System.Convert.ToInt32(data[1]);
			Mappings[inpIndex] = previewIndex;
			UpdateMapDisplay(inpIndex);
		}

		private void MapColumnToImport_Click(object sender, System.EventArgs e)
		{
			// only for preview grid
			MenuItem mi = sender as MenuItem;
			// get column code
			String[] data = mi.Tag.ToString().Split(',');

			int previewIndex = System.Convert.ToInt32(data[0]);
			int inpIndex = System.Convert.ToInt32(data[1]);
			Mappings[inpIndex] = previewIndex;
			UpdateMapDisplay(inpIndex);
		}

		private void UnmapColumn_Click(object sender, System.EventArgs e)
		{
			// only for preview grid
			MenuItem mi = sender as MenuItem;
			// get column code
			string data = mi.Tag.ToString();
			int colIndex = System.Convert.ToInt32(data);
			int inpIndex = Mappings.IndexOf(colIndex);
			if (inpIndex > -1)
				Mappings[inpIndex] = -1;
			ClearDisplay(colIndex);
		}

		private void UnmapInputColumn_Click(object sender, System.EventArgs e)
		{
			// only for input grid
			MenuItem mi = sender as MenuItem;
			// get column code
			string data = mi.Tag.ToString();
			int inpIndex = System.Convert.ToInt32(data);
			int previewIndex = Mappings[inpIndex];
			if (inpIndex > -1)
				Mappings[inpIndex] = -1;
			ClearDisplay(previewIndex);
		}

		private void SetUI(int state)
		{
			switch (state)
			{
				case 1:
					//this.btnClose.Enabled = false;
					this.btnImport.Text = "Cancel";
					break;
				case 0:
					//this.btnClose.Enabled = true;
					this.btnImport.Text = "Import";
					//this.progressBar1.Value = 0; // clear the progress bar.
					break;
			}
		}



		//////////////////////////////////////////////////////////////////////////////////////////////////
		// Work happens from here
		private void DoCancel()
		{
			if (MessageBox.Show(this, "Cancel Import", "Cancel", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
				this.userCancel = true;
		}

		private void DoImport()
		{
			UpdateImportMode();
			// get 
			string modeDescription = (this.m_ImportMode == DbViewDriver.CsvImportMode.Replace) ? CONFIRM_REPLACE :
									(this.m_ImportMode == DbViewDriver.CsvImportMode.Merge) ? CONFIRM_MERGE :
									(this.m_ImportMode == DbViewDriver.CsvImportMode.UpdateOnly) ? CONFIRM_UPDATE :
									(this.m_ImportMode == DbViewDriver.CsvImportMode.AppendOnly) ? CONFIRM_APPEND : "";
			string message;
			if (!IsOkToStart(out message))
			{
				MessageBox.Show(this, message, "Cannot proceed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return;
			}
			//
			// All checks made that might prevent sucessful operation. Now ask the user to confirm it's ok.
			//
			string confirmProceed = string.Format(
@"Please read the description of the import operation and confirm that is is suitable:

{0}

Proceed with import. Press No to select a different import mode", string.Format(modeDescription, TableName));
			if (MessageBox.Show(this, confirmProceed, "Comfirm import", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
				return;


			this.importWorkerThread = new Thread(new ParameterizedThreadStart(ImportCsvForm.DoImport)); // create new thread with this object
			this.importWorkerThread.IsBackground = true;												// make it a daemon - prevent thread callback issues 

			SetUI(1);			// disable controls you dont want fiddling with (rememer [x] button
			d = Progress;		// assign the delegate (can't be done at declaration for some reason.
			this.userCancel = false;	// reset the cancel flag

			this.importWorkerThread.Start(this);		// Start the work
		}

		// Check Preconditions
		bool IsOkToStart(out string message)
		{
			message = "OK";
			// must map at least one column.
			bool mapCheck = false;

			DbView.FieldInfo[] schema = Database.GetSchema(TableName);
			List<String> pkeys = new List<string>();
			List<String> nonNullNoDflt = new List<string>();
			foreach (DbView.FieldInfo fi in schema)
			{
				if (fi.IsPKey)
					pkeys.Add(fi.Name);
				else
					if (!fi.AllowsNulls && !fi.HasDefault)
						nonNullNoDflt.Add(fi.Name);
			}
			// check for selection of an import mode that requires a Pkey selected for a table
			// that does not contain a pkey
			if (pkeys.Count == 0 && this.m_ImportMode != DbViewDriver.CsvImportMode.Replace) // only replace is allowable others require pkey matching
			{
				message = string.Format("You have selected an import mode that requires the target table to " +
										"possess a primary key. However table {0} does not have a primary key. " +
										"The only data import option available for {0} " +
										"is replacement, where the current data is deleted and replaced with data from " +
										"the CSV file. Please adjust the import mode to Replace or cancel the operation."
										, TableName);

				return false;
			}

			// Check for fields which do not allow nulls and have no default and empty cells
			for (int importCol = 0; importCol < Mappings.Count; ++importCol)
			{
				int col = Mappings[importCol];
				if (col != -1)
				{
					string currentField = schema[col].Name;
					mapCheck = true;
					pkeys.Remove(currentField);
					// check Non-null no default and missing data
					if (nonNullNoDflt.IndexOf(currentField) != -1)
					{
						if (hasNullsInImportData(importCol))
						{
							message = string.Format("Field [ {0} ] does not allow nulls, but is mapped to a column \n" +
													"in the import file that contains empty values. This will cause \n" +
													"the import to fail. ", currentField);
							return false;
						}
					}
					nonNullNoDflt.Remove(currentField);

				}
			}

			if (!mapCheck)
			{
				message = "Please map at least one input column before importing CSV data";
				return false;
			}

			// add check for all primary keys 
			if (pkeys.Count > 0)
			{
				message = string.Format("You need to map Primay key field(s) {0} or the import will fail", String.Join(", ", pkeys.ToArray()));
				return false;
			}
			// 
			if (nonNullNoDflt.Count > 0 && this.m_ImportMode != DbViewDriver.CsvImportMode.UpdateOnly)
			{
				string nnTmpl = "You need to map field(s): {0} or the import will fail. A field\n" +
								"that does not allow nulls and has no default will cause an insert\n" +
								"to fail if it is not included. To remedy this either:\n" +
								"  a. Map the field(s): {0} to import data or \n" +
								"  b. Switch import mode to Update only\n" +
								"whichever seems more suitable";
				message = string.Format(nnTmpl, String.Join(", ", nonNullNoDflt.ToArray()));
				return false;
			}
			if (!ConnectionAndRowCountCheckOk(TableName, out message))
			{
				return false;
			}

			return true;
		}

		private bool hasNullsInImportData(int importCol)
		{
			DataTable inp = dgvInput.DataSource as DataTable;

			foreach (DataRow dr in inp.Rows)
			{
				if (dr[importCol].ToString().Length == 0)
					return true;
			}
			return false;
		}


		private bool ConnectionAndRowCountCheckOk(string tableName, out string message)
		{
			message = "OK";
			try
			{
				int rowCount = Database.CountRows(tableName, "");
				//if (rowCount > 0)
				//{
				//    message = "Please delete all rows from "+tableName+" before importing CSV data";
				//    return false;
				//}
			}
			catch (Exception e)
			{
				message = e.Message;
				return false; // assume some kind of sql error
			}

			return true; // ok to go
		}






		// version of above for uploading in chunks
		public static void DoImport(object caller)
		{
			// obtain the object with all the runtime info.
			// as this is a static method of the same class as the object it has
			// access to the private data of the object. That's neat!
			ImportCsvForm form = caller as ImportCsvForm;
			int runState = 1;			// running
			bool wantStaging = true;	// will be true unless populating a completely empty table
			// we don't really want to encourage splitting the operation
			// because there is a slight connection overhead with each chunk.
			// otoh we don't want the routine to be an uncaring memory hog either 
			// 
			int loadChunkSize = 10000000;  // 10,000,000 byte chunking.
			// anything could happen in here. We need error trapping
			try
			{
				// Since we are uploading into a staging table we need to create one
				form.Database.CreateStagingTable(form.TableName, wantStaging, true);
				// find file size for sensible progress calculation
				long fileSize = new FileInfo(form.InputCsvFile).Length;

				// create a DataTable in a suitable format for bulk updates.
				DataTable dt = form.Database.CreateTransferTable(form.TableName);
				int[] mappings = form.Mappings.ToArray();
				// get the fields
				DbView.FieldInfo[] fields = form.Database.GetSchema(form.TableName);

				// load data into the DataTable from the CSV field and the mapping configuration
				StreamReader sr = File.OpenText(form.InputCsvFile);
				Csv.CsvReader reader = new Csv.CsvReader();
				reader.FieldPadding = false; // we don't need the parser to pad. It's a fraction quicker.
				string input;
				// initialise state
				bool done = false;
				bool first = (form.HasHeader);
				int bytesRead = 0;
				int linesRead = 0;			// track lines read
				int progressLineLimit = 100;   // prevent too many callbacks
				int bytesToLoad = 0;
				/////////////////////////////////////////////////////////////////////////////
				// Csv file read loop
				while ((input = sr.ReadLine()) != null)
				{
					done = reader.ParseCsvLine(input);
					if (done)
					{
						if (first)
							first = false;
						else
						{
							String[] cells = reader.Data;				// load all from the input
							DataTableHelpers.AddRow(dt, cells, mappings); // add only those mapped.
						}
					}
					bytesRead += input.Length; // Crlf
					bytesToLoad += input.Length; // Crlf

					// time to report progress and check for cancellation
					if ((++linesRead % progressLineLimit) == 1)
					{
						if ((bool)form.Invoke(form.d, new object[] { "Read Csv file ...", bytesRead, (int)fileSize, runState }))
						{
							// cancelled
							runState = 2; // save -1 for system error
							break;			// breaks out of the file read loop
						}
					}
					// time to transfer to the database
					if (bytesToLoad > loadChunkSize)
					{
						form.Database.PopulateTable(form.TableName, dt, wantStaging);
						// reset data buffer and state
						dt.Rows.Clear();
						bytesToLoad = 0;
					}
				}
				sr.Close();

				// abort on cancel here
				if (runState != 2) // 2 means cancel state 
				{
					// deal with remaining data
					if (bytesToLoad > 0)
						form.Database.PopulateTable(form.TableName, dt, wantStaging);
					runState = 0;
					// if wantStaging is true we have only populated the staging table at this point.
					// we now need to update the target table according to the mode selected.
					String[] mappedFields = GetMappedFields(mappings, dt);
					form.Database.UpdateFromStaging(form.TableName, mappedFields, form.m_ImportMode);
				}
				// important last call to let the form know we're done one way or another,
				form.Invoke(form.d, new object[] { "Completed", (runState == 2) ? 0 : (int)fileSize, (int)fileSize, runState });
			}
			catch (Exception generalException)
			{
				runState = -1;
				// pass error state to UI
				form.Invoke(form.d, new object[] { generalException.GetType().ToString() + ": " + generalException.Message, 0, 1, runState });
			}
			// check remove staging table 
			form.Database.CreateStagingTable(form.TableName, wantStaging, false);
		}

		private static string[] GetMappedFields(int[] mappings, DataTable dt)
		{
			List<String> fields = new List<string>();
			// 
			foreach (int mapping in mappings)
			{
				if (mapping != -1)
					fields.Add(dt.Columns[mapping].ColumnName);
			}
			return fields.ToArray();
		}

		// progress callback
		bool Progress(String op, int step, int steps, int state)
		{
			if (ImportProgress.Maximum != steps)
				ImportProgress.Maximum = steps;
			ImportProgress.Value = step;
			switch (state)
			{
				case -1: // error
					// lblOperation.Text = "Aborted";
					MessageBox.Show(this, op, "Error in import", MessageBoxButtons.OK, MessageBoxIcon.Error);
					SetUI(0);
					break; // done
				case 1:
					if (this.userCancel)
					{
						MessageBox.Show(this, "Import Cancelled by user action", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information);
						ImportProgress.Value = 0;
						return true; // cancel
					}
					break; // running
				case 0:
					SetUI(0);
					MessageBox.Show(this, "Import to " + TableName + " complete", "CSV Import", MessageBoxButtons.OK, MessageBoxIcon.Information);
					this.Close();
					break; // done
				case 2:
					SetUI(0);
					break; // cancelled
			}

			return false;
		}

		// Work state 
		bool IsWorking()
		{
			return (btnImport.Text == "Cancel"); // hacky.
		}



		// prevent user from exiting while the actual import is in progress,
		private void Form6_FormClosing(object sender, FormClosingEventArgs e)
		{
			if (e.CloseReason == CloseReason.UserClosing && IsWorking())
			{
				MessageBox.Show(this, CANT_CLOSE, "Cannot close right now", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				e.Cancel = true; // suppress. Better would be to ask
			}
		}

		const string TITLE =
		@"Import data from a file into a table. The file contents are previewed in the top grid. Check the box if there is a header row as this map help to map the columns. " +
		@"Map columns before importing. Press Automap either to use the import file header row or make a sequential mapping (column 1 -> field 1). For custom mapping right-click " +
		@"on the column headings of either grid to map or ummap items. Select the import mode, which gives you various options to completely replace data in the table or update it." +
		@"Press Import to begin.";

		const string CONFIRM_REPLACE = @"All rows in table {0} will be deleted and replaced with the data from the CSV file";
		const string CONFIRM_MERGE = @"Data from the CSV file will add new rows and update existing rows in table {0}.";
		const string CONFIRM_UPDATE = @"Data from the CSV file will be used to update existing rows in table {0}, but no new rows will be added";
		const string CONFIRM_APPEND = @"Data from the CSV file will add new rows to table {0}, but no existing rows in the table will be updated.";

		const string CANT_CLOSE = "Program is still working. Please cancel the current operation before closing";
	}
}
